package com.clotingshoppos.modules.order.repository;

import com.clotingshoppos.common.base.BaseRepository.BaseRepository;
import com.clotingshoppos.modules.order.POJO.CountModel;
import com.clotingshoppos.modules.order.entity.OrderDetailEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.List;
import java.util.Optional;

/**
 * @InterfaceName: OrderDetailRepository
 * @Description: 订单详表数据库接口
 * @Author: zhandad
 * @Date: 2019-03-14 22:41
 * @Version
 */
@Repository
public interface OrderDetailRepository extends BaseRepository<OrderDetailEntity> {
    @Query(value = "select * from order_detail where order_id = :orderId", nativeQuery = true)
    Optional<List<OrderDetailEntity>> findByOrderId(@Param("orderId") String orderId);

    @Query(value = "select new OrderDetailEntity (product, sum(number)) from OrderDetailEntity " +
            "where orderId in (select id from OrderEntity  where status = '1') " +
            "group by product")
    List<OrderDetailEntity> countByProduct();

    @Query(value = "select new OrderDetailEntity (product, sum(number), sum(totalPrice)) from OrderDetailEntity " +
            "where orderId in (select id from OrderEntity  where status = '1' and stuff = :stuff and updateTime between :time1 and :time2) " +
            "group by product")
    List<OrderDetailEntity> countByProductByStuff(@Param("stuff") String stuff, @Param("time1") Timestamp time1, @Param("time2") Timestamp time2);

    @Query(value = "select new OrderDetailEntity (product, sum(number)) from OrderDetailEntity " +
            "where orderId in (select id from OrderEntity  where status = '1' and updateTime between :time1 and  :time2) " +
            "group by product")
    List<OrderDetailEntity> countByProductWithTime(@Param("time1") Timestamp time1, @Param("time2") Timestamp time2);
}
